﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DAProva
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Function Cadastra(ByRef objBEProva As BEProva) As Integer
    Dim intIdProva As Integer = 0
    Try
      objBD.duplicaAspasSimples(objBEProva, GetType(BEProva))
      intIdProva = objBD.ExecutaEscalar("INSERT INTO ext_provas (codigo, titulo, tempo) " & _
                                        "VALUES ('" & objBEProva.Codigo & "', '" & _
                                                      objBEProva.Titulo & "', '" & _
                                                      objBEProva.TempoTotal & "');" & _
                                        "SELECT LAST_INSERT_ID();")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.Cadastra(): " & ex.Message)
    End Try
    Return intIdProva
  End Function

  Public Sub Atualiza(ByVal objBEProva As BEProva)
    Try
      objBD.duplicaAspasSimples(objBEProva, GetType(BEProva))
      objBD.ExecutaNonQuery("UPDATE ext_provas " & _
                            "SET codigo='" & objBEProva.Codigo & "', " & _
                                "titulo='" & objBEProva.Titulo & "', " & _
                                "tempo='" & objBEProva.TempoTotal & "' " & _
                            "WHERE id=" & objBEProva.Id & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Public Sub RelacionaProvaQuestao(ByVal objBEProva As BEProva)
    Try
      objBD.ExecutaNonQuery("INSERT INTO ext_rel_prova_questoes (provaId, questaoId, ordem)" & _
                            "VALUES (" & objBEProva.Id & ", " & objBEProva.Questoes(0).Id & ", " & _
                            "(SELECT (CASE WHEN ordem IS NULL THEN 0 ELSE ordem END)+1 " & _
                            "FROM (SELECT MAX(ordem) AS ordem FROM ext_rel_prova_questoes " & _
                                  "WHERE provaid=" & objBEProva.Id & ") DERIVEDTBL));")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.RelacionaVagaProva(): " & ex.Message)
    End Try
  End Sub

  Public Sub MudaOrdemQuestao(ByVal objBEProva As BEProva)
    Dim strSql As String = "UPDATE ext_rel_prova_questoes SET ordem="
    objQuery._Select = "SELECT ordem FROM ext_rel_prova_questoes"
    objQuery.Where.AdicionarCondicaoWhereFixo("provaid=" & objBEProva.Id & " AND questaoid=" & objBEProva.Questoes(0).Id)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strSql &= objSQLDReader("ordem") & " " & _
                     "WHERE provaid=" & objBEProva.Id & " AND ordem=" & objBEProva.Questoes(0).Ordem & ";"
          End While
        End Using
      End Using
      strSql &= "UPDATE ext_rel_prova_questoes SET ordem=" & objBEProva.Questoes(0).Ordem & " " & _
               "WHERE provaid=" & objBEProva.Id & " AND questaoid=" & objBEProva.Questoes(0).Id & ";"
      objBD.ExecutaNonQuery(strSql)
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAVaga.MudaOrdemQuestao(): " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBEProva As BEProva)
    Dim strSql As String = "UPDATE ext_vagas SET provaId=NULL WHERE provaId=" & objBEProva.Id & ";"
    objQuery._Select = "SELECT questaoid FROM ext_rel_prova_questoes"
    objQuery.Where.AdicionarCondicaoWhereFixo("provaid=" & objBEProva.Id)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            strSql &= "DELETE FROM ext_alternativas_questao WHERE questaoid=" & objSQLDReader("questaoid") & ";" & _
                      "DELETE FROM ext_questoes WHERE id=" & objSQLDReader("questaoid") & ";"
          End While
        End Using
      End Using
      strSql &= "DELETE FROM ext_rel_prova_questoes WHERE provaid='" & objBEProva.Id & "';" & _
                "DELETE FROM ext_provas WHERE id='" & objBEProva.Id & "';"
      objBD.ExecutaNonQuery(strSql)
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function ResumoDaProva(ByVal intIdCandidato As Integer, ByVal intIdProva As Integer) As BEProva
    Dim objBEProva As BEProva = Nothing
    Try
      objQuery._Select = "SELECT p.*, (SELECT tempo_prova FROM ext_rel_candidato_vagas WHERE candidatoid=" & intIdCandidato & " AND vagaid=(SELECT id FROM ext_vagas WHERE provaid=p.id)) tempo_transcurrido, " & _
                         "(SELECT count(*) FROM ext_rel_prova_questoes WHERE provaid=p.id) NumeroQuestoes, " & _
                         "(SELECT count(*) FROM ext_notas_candidato WHERE provaid=p.id AND candidatoid=" & intIdCandidato & ") QuestoesRespondidas, " & _
                         "(SELECT count(*) FROM ext_notas_candidato nc INNER JOIN ext_questoes q ON nc.questaoid=q.id " & _
                         "WHERE nc.candidatoid=" & intIdCandidato & " AND provaid=p.id AND ((q.respostaDissertativa=1 AND nc.respostaDissertativaCerta=1) OR nc.alternativaid=" & _
                         "(SELECT aq.id FROM ext_alternativas_questao aq WHERE nc.questaoid=aq.questaoid AND aq.alternativacerta=1))) RespostasCertas " & _
                         "FROM ext_provas p where p.id=" & intIdProva
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEProva = New BEProva()
            With objBEProva
              .Id = objSQLDReader("id")
              .Codigo = objSQLDReader("codigo").ToString()
              .Titulo = objSQLDReader("titulo").ToString()
              .TempoTotal = objSQLDReader("tempo").ToString()
              .TempoTranscurrido = TimeSpan.Parse(objSQLDReader("tempo_transcurrido").ToString() & ":00")
              .NumeroDeQuestoes = objSQLDReader("NumeroQuestoes")
              .NumeroDeQuestoesRespondidas = objSQLDReader("QuestoesRespondidas")
              .RespostasCertas = objSQLDReader("RespostasCertas")
            End With
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.ResumoDaProva(): " & ex.Message)
    End Try
    Return objBEProva
  End Function

  Public Function ResumoDaProva(ByVal strAluno As String, ByVal intIdProva As Integer) As BEProva
    Dim objBEProva As BEProva = Nothing
    Try
      objQuery._Select = "SELECT p.*, (SELECT tempo_prova FROM ext_rel_alunos_cursos WHERE usuario='" & strAluno & "' AND idCurso=(SELECT id FROM ext_cursos WHERE provaid=p.id)) tempo_transcurrido, " & _
                         "(SELECT count(*) FROM ext_rel_prova_questoes WHERE provaid=p.id) NumeroQuestoes, " & _
                         "(SELECT count(*) FROM ext_respostas_aluno WHERE provaid=p.id AND usuario='" & strAluno & "') QuestoesRespondidas, " & _
                         "(SELECT count(*) FROM ext_respostas_aluno ra INNER JOIN ext_questoes q ON ra.questaoid=q.id " & _
                         "WHERE usuario='" & strAluno & "' AND provaid=p.id AND ((q.respostaDissertativa=1 AND ra.respostaDissertativaCerta=1) OR ra.alternativaid=" & _
                         "(SELECT aq.id FROM ext_alternativas_questao aq WHERE ra.questaoid=aq.questaoid AND aq.alternativacerta=1))) RespostasCertas " & _
                         "FROM ext_provas p where p.id=" & intIdProva
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEProva = New BEProva()
            With objBEProva
              .Id = objSQLDReader("id")
              .Codigo = objSQLDReader("codigo").ToString()
              .Titulo = objSQLDReader("titulo").ToString()
              .TempoTotal = objSQLDReader("tempo").ToString()
              .TempoTranscurrido = TimeSpan.Parse(objSQLDReader("tempo_transcurrido").ToString() & ":00")
              .NumeroDeQuestoes = objSQLDReader("NumeroQuestoes")
              .NumeroDeQuestoesRespondidas = objSQLDReader("QuestoesRespondidas")
              .RespostasCertas = objSQLDReader("RespostasCertas")
            End With
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.ResumoDaProva(): " & ex.Message)
    End Try
    Return objBEProva
  End Function

  Public Function Seleciona(ByVal objBEProva As BEProva) As List(Of BEProva)
    Dim objBEProvas As List(Of BEProva) = New List(Of BEProva)
    Try
      objBD.duplicaAspasSimples(objBEProva, GetType(BEProva))
      objQuery._Select = "SELECT * FROM ext_provas"
      If objBEProva IsNot Nothing Then
        If objBEProva.Id > 0 Then objQuery.Where.AdicionarCondicaoWhere("id", objBEProva.Id, False)
        objQuery.Where.AdicionarCondicaoWhere("titulo", objBEProva.Titulo)
      End If
      objBEProvas = SelecionaProvas()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.Seleciona(): " & ex.Message)
    End Try
    Return objBEProvas
  End Function

  Private Function SelecionaProvas() As List(Of BEProva)
    Dim objBEProvas As List(Of BEProva) = New List(Of BEProva)
    Dim objBEProva As BEProva
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBEProva = New BEProva()
            With objBEProva
              .Id = objSQLDReader("id")
              .Codigo = objSQLDReader("codigo").ToString()
              .Titulo = objSQLDReader("titulo").ToString()
              .TempoTotal = objSQLDReader("tempo").ToString()
            End With
            objBEProvas.Add(objBEProva)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAProva.SelecionaProvas(): " & ex.Message)
    End Try
    Return objBEProvas
  End Function

  Public Function NumeroQuestoes(ByVal objBEProva As BEProva) As Integer
    Dim intNumeroQuestoes As Integer = 0
    objQuery._Select = "SELECT count(*) FROM ext_rel_prova_questoes " & _
                       "WHERE provaid=" & objBEProva.Id
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            intNumeroQuestoes = objSQLDReader(0)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.CandidatoDAO.NumeroQuestoes(): " & ex.Message)
    End Try
    Return intNumeroQuestoes

  End Function

End Class
